﻿-- Limpiar DEMO
--
DROP AGGREGATE Aggregates.CONCAT_AGG
DROP AGGREGATE Aggregates.CONCAT_AGG_Optimized
DROP FUNCTION dbo.GetSignature
DROP FUNCTION dbo.EsMarcaFox
DROP FUNCTION dbo.BuscarTexto
DROP FUNCTION dbo.BuscarTextoCI
DROP FUNCTION dbo.EsNumeroParClr
DROP FUNCTION dbo.EsNumeroParClrDataAccess
DROP PROCEDURE dbo.Bucle_NonYield
DROP PROCEDURE dbo.Bucle_Preemtive
DROP PROCEDURE dbo.Bucle_Yield
DROP PROCEDURE dbo.MyStoredProcedureCLR
DROP PROCEDURE dbo.PerformanceAccesoDatosCLR
DROP PROCEDURE dbo.PerformanceAccesoDatosCLR_ManualSend
DROP ASSEMBLY training
DROP ASSEMBLY CustomFunctions


-- Versión CLR
--
SELECT SERVERPROPERTY('BuildClrVersion')

-- Estado del CLR
--
SELECT name,description, value
FROM sys.configurations WHERE name = 'clr enabled'

-- Desplegar ensamblado manualmente
CREATE ASSEMBLY CustomFunctions
   AUTHORIZATION dbo
   FROM 'C:\TFS_ecb\WebcastsYPresentaciones\SQLCLR\Training\bin\Release\Training.dll'
   WITH PERMISSION_SET = SAFE;

/*
CREATE ASSEMBLY [CustomFunctions]
    AUTHORIZATION [dbo]
    FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300C1E8ED540000000000000000E00002210B010B000018000000060000000000000E360000002000000040000000000010002000000002000004000000000000000600000000000000008000000002000000000000030060850000100000100000000010000010000000000000100000000000000000000000B43500005700000000400000A802000000000000000000000000000000000000006000000C0000007C3400001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E7465787400000014160000002000000018000000020000000000000000000000000000200000602E72737263000000A80200000040000000040000001A0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001E00000000000000000000000000004000004200000000000000000000000000000000F035000000000000480000000200050054230000281100000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000133002001700000001000011166A0A2B0506176A580A062100C3CA010200000031EF2A00133002003200000002000011166A0A2B2306166A310616280600000A166A0B2B0506176A580A0720B07280006A31F206176A580A0620000400006A31D42A0000133002002100000001000011280700000A166A0A2B0506176A580A062100C3CA010200000031EF280800000A2A1E02280900000A2ACA0F00280B00000A2C067E0C00000A2A0F00280D00000A0F01280D00000A6F0E00000A2C0717280F00000A2A16280F00000A2AC20F00280B00000A2C067E0C00000A2A0F00280D00000A72010000706F0E00000A2C0717280F00000A2A16280F00000A2A000000133003003600000003000011026F1000000A2C06281100000A2A7E01000004026F1200000A0A1200280D00000A72090000706F1300000A281400000A731500000A2A46720D0000701E731600000A80010000042A1E02280900000A2A3202731A00000A7D020000042AA20F01280B00000A2C012A027B020000040F01280D00000A6F1B00000A72100400706F1B00000A262A52027B020000040F017B020000046F1C00000A262A00133004003D000000040000117E1D00000A0A027B020000042C28027B020000046F1E00000A16311A027B0200000416027B020000046F1E00000A17596F1F00000A0A06732000000A2A4A02036F2100000A732200000A7D020000042A4A03027B020000046F2300000A6F2400000A2A3202731A00000A7D030000042A033003004E00000000000000036F1000000A2C012A04282500000A2C23027B03000004161F2F6F2600000A26027B0300000416036F2700000A6F2800000A262A027B03000004036F2700000A6F2900000A1F2F6F2A00000A262A52027B030000040F017B030000046F1C00000A262A00133004003D000000040000117E1D00000A0A027B030000042C28027B030000046F1E00000A16311A027B0300000416027B030000046F1E00000A17596F1F00000A0A06732000000A2A4A02036F2100000A732200000A7D030000042A4A03027B030000046F2300000A6F2400000A2A0042534A4201000100000000000C00000076342E302E33303331390000000005006C0000005C040000237E0000C80400006804000023537472696E677300000000300900001404000023555300440D0000100000002347554944000000540D0000D403000023426C6F620000000000000002000001571702000900000000FA25330016000001000000190000000500000003000000150000000D000000020000002A0000000C00000004000000010000000300000000000A0001000000000006006D0066000600740066000A00A5008A000A00FC00E7000A000701E7000E00470128010A006101E7000600830177010600C501BB010600D701BB010A001E028A0006004F023C023300630200000600920272020600B20272020A00D9028A0006000003EF020A0033038A000600620366000E00A20328010600AF0366000A00C5038A000A00E6038A0006000C04ED0306002204ED03000000000100000000000100010001001000170000000500010001000100100028000000050001000500092110003D000000090002000A00092110004800000009000300100031004D012200010091012D00010091012D005020000000009600B6000A0001007420000000009600C5000A000100B420000000009600D1000A000100E120000000008618E1000E000100E9200000000096001101120001001C210000000096001D011B00030050210000000096006A0126000400A421000000008618E1000E00050092210000000091189B030A000500AC210000000086009B010E000500B921000000008600A00131000500E221000000008600AB0137000600F821000000008600B1013D000700412200000000E601D20142000700542200000000E601E4014800080067220000000086009B010E0009007422000000008600A0014E000900CE22000000008600AB0156000B00E422000000008600B1013D000C002D2300000000E601D20142000C00402300000000E601E40148000D0000000100EA0100000200F60100000100050200000100EA01000001000E02000001001402000001001A02000001001C02000001000E02000002003002000001001402000001001A02000001001C0204000D0005000D005900E1000E006100E1006F007100E10075007900E1000E008100E1000E0089000703830089000D030A00890021030A000900E1000E009100E1000E00290048038D00210053039100290058039500990069039900210072039E00390048038D0039007E031902390087033D00310093031E022900720324023900E10031003100E1002F02A900E1000E00B100E1003602C100E100C2024100E1000E0041002D04C80241002D04CE0299003404D40241003A04D70241004504DB022900E100E10249004E0495004100E100E1020900450495005100E401E102210059047A03410061048003390058038703410061048C0341002D04940341002D049B0320002B007A002E002300B3032E001300A1032E001B00AA0340002B007A0060002B007A008300C3003C02A00053007A00A300C300EA02C0005300A400E0005300610124010B005C007F0088002A02E602048000000000000000000000000000000000D002000004000000000000000000000001005D000000000004000000000000000000000001007E000000000004000000000000000000000001006600000000000000003C4D6F64756C653E00547261696E696E672E646C6C0053746F72656450726F636564757265730055736572446566696E656446756E6374696F6E7300434F4E4341545F41474700434F4E4341545F4147475F4F7074696D697A6564006D73636F726C69620053797374656D004F626A6563740056616C7565547970650053797374656D2E44617461004D6963726F736F66742E53716C5365727665722E536572766572004942696E61727953657269616C697A65004275636C655F4E6F6E5969656C64004275636C655F5969656C64004275636C655F507265656D74697665002E63746F720053797374656D2E446174612E53716C54797065730053716C426F6F6C65616E0053716C537472696E6700427573636172546578746F0045734D61726361466F780053797374656D2E546578742E526567756C617245787072657373696F6E7300526567657800436F6E7374616E747345787072657373696F6E0053716C4368617273004765745369676E61747572650053797374656D2E5465787400537472696E674275696C64657200726573756C7461646F00496E697400416363756D756C617465004D65726765005465726D696E6174650053797374656D2E494F0042696E61727952656164657200526561640042696E617279577269746572005772697465007175657279737472696E6700746578746F5F615F6275736361720070726F647563746F0056616C75650047726F7570007200770053716C4661636574417474726962757465007072696D657256616C6F720053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C69747941747472696275746500547261696E696E670053716C50726F6365647572654174747269627574650053797374656D2E546872656164696E670054687265616400536C65657000426567696E546872656164416666696E69747900456E64546872656164416666696E6974790053716C46756E6374696F6E417474726962757465006765745F49734E756C6C004E756C6C006765745F56616C756500537472696E6700436F6E7461696E73006F705F496D706C69636974006765745F4E756C6C00546F53716C537472696E67005265706C616365002E6363746F720052656765784F7074696F6E730053657269616C697A61626C654174747269627574650053716C55736572446566696E656441676772656761746541747472696275746500466F726D61740053797374656D2E52756E74696D652E496E7465726F705365727669636573005374727563744C61796F7574417474726962757465004C61796F75744B696E6400417070656E6400456D707479006765745F4C656E67746800546F537472696E670052656164537472696E67006F705F5472756500496E7365727400000746004F0058000003230000840128005B005C0073002C0028003D003C003E0021005D0028003F0021005B005E005C0027005D005D002B005B005C005D005D002900290028003F003A0028003F003A0028003F003A0028003F0023002000200020002000650078007000720065007300730069006F006E00200063006F006D0069006E0067000D000A0020002000200020002000200020002000200020002000200020002000200020002000290028003F003A0028005B004E005D0029003F0028002700290028003F003A005B005E0027005D0029002B00280027002900290028003F0023002000200020002000200020002000200020002000200020006300680061007200610063007400650072000D000A002000200020002000200020002000200020002000200020002000200020002000200029007C0028003F003A00300078005B005C00640061002D00660041002D0046005D002A00290028003F002300200020002000200020002000200020002000200020002000200020002000200020002000200020002000620069006E006100720079000D000A002000200020002000200020002000200020002000200020002000200020002000200029007C0028003F003A005B002D002B005D003F0028003F003A0028003F003A005B005C0064005D002A005C002E005B005C0064005D002A007C005B005C0064005D002B00290028003F00230020002000200020002000700072006500630069007300650020006E0075006D006200650072000D000A0020002000200020002000200020002000200020002000200020002000200020002000290028003F003A005B00650045005D003F005B005C0064005D002A0029002900290028003F0023002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200069006D00700072006500630069007300650020006E0075006D006200650072000D000A002000200020002000200020002000200020002000200020002000200020002000200029007C0028003F003A005B007E005D003F005B002D002B005D003F0028003F003A005B005C0064005D002B002900290028003F002300200020002000200020002000200020002000200020002000200020002000200069006E00740065006700650072000D000A00200020002000200020002000200020002000200020002000200020002000200020002900290028003F003A005B005C0073005D003F005B005C002B005C002D005C002A005C002F005C0025005C0026005C007C005C005E005D005B005C0073005D003F0029003F0029002B0028003F0023002000200020006F00700065007200610074006F00720073000D000A00200020002000200020002000200020002000200020002000200020002000200020002900290001032C000036A47B9D924BCF4D9A16DCCB8761793A0008B77A5C561934E08903000001032000010800021111111511150600011111111503061219060001121D121D03061221052001011115052001011110042000111505200101122505200101122907200201121D111105200101111412010001005408074D617853697A650A000000052001011135042001010804010000000307010A04000101080407020A0A03200002030611110320000E042001020E05000111110280BB0100030054557F4D6963726F736F66742E53716C5365727665722E5365727665722E53797374656D446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D342E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038391053797374656D446174614163636573730000000054020F497344657465726D696E6973746963015402094973507265636973650180B601000300540E044E616D650C4765745369676E617475726554020F497344657465726D696E6973746963015455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D342E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A4461746141636365737300000000040000121D0520020E0E0E05000111150E0407011115062002010E115105200101115D808401000200000006005402124973496E76617269616E74546F4E756C6C73005402124973496E76617269616E74546F4F72646572005402174973496E76617269616E74546F4475706C6963617465730054020D49734E756C6C4966456D7074790154080B4D61784279746553697A65401F0000540E044E616D650A434F4E4341545F41474705200101116505200112210E05200112211C02060E032000080520020E0808042001010E0307010E808E01000200000006005402124973496E76617269616E74546F4E756C6C73005402124973496E76617269616E74546F4F72646572005402174973496E76617269616E74546F4475706C6963617465730054020D49734E756C6C4966456D7074790154080B4D61784279746553697A65401F0000540E044E616D6514434F4E4341545F4147475F4F7074696D697A6564050001021111062002122108030420001D030720021221081D0306200112211D030520011221030801000200000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301000000000000C1E8ED5400000000020000001C010000983400009816000052534453A946BEB73A616146AF9ABF25D9DD0F6901000000633A5C5446535F6563625C57656263617374735950726573656E746163696F6E65735C53514C434C525C547261696E696E675C6F626A5C52656C656173655C547261696E696E672E7064620000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000DC3500000000000000000000FE350000002000000000000000000000000000000000000000000000F03500000000000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500200010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001001000000018000080000000000000000000000000000001000100000030000080000000000000000000000000000001000000000048000000584000004C02000000000000000000004C0234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004AC010000010053007400720069006E006700460069006C00650049006E0066006F0000008801000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E00300000003C000D00010049006E007400650072006E0061006C004E0061006D006500000054007200610069006E0069006E0067002E0064006C006C00000000002800020001004C006500670061006C0043006F00700079007200690067006800740000002000000044000D0001004F0072006900670069006E0061006C00460069006C0065006E0061006D006500000054007200610069006E0069006E0067002E0064006C006C0000000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E0030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000003000000C000000103600000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000;
*/
CREATE FUNCTION [dbo].[BuscarTexto]
       (
        @querystring nvarchar(MAX),
        @texto_a_buscar nvarchar(MAX)
       )
RETURNS bit
AS EXTERNAL NAME CustomFunctions.[UserDefinedFunctions].[BuscarTexto]


-- Test-run 1
SELECT Val,dbo.BuscarTexto(Val,'net') AS Result
FROM (SELECT 'este texto no tiene que detectarse' AS Val UNION ALL
      SELECT 'este net texto SI que tiene que detectarse' AS Val UNION ALL
	  SELECT 'este texto tampoco tiene que detectarseNET' AS Val) a
go





CREATE FUNCTION [dbo].[BuscarTextoCI]
       (
        @querystring nvarchar(MAX),
        @texto_a_buscar nvarchar(MAX)
       )
RETURNS bit
AS EXTERNAL NAME CustomFunctions.[UserDefinedFunctions].[BuscarTextoCI]


SELECT Val,dbo.BuscarTextoCI(Val,'net') AS Result
FROM (SELECT 'este texto no tiene que detectarse' AS Val UNION ALL
      SELECT 'este net texto SI que tiene que detectarse' AS Val UNION ALL
	  SELECT 'este texto tambien tiene que detectarseNET' AS Val) a
go






SET STATISTICS TIME ON
GO
USE Training
go

SELECT TextData
FROM ::fn_trace_gettable('C:\TFS_ecb\WebcastsYPresentaciones\SQLCLR\Miscelaneo\traza_2015-01-22 092136_359.trc',1)
WHERE eventclass = 12
AND TextData LIKE '%W881233%'
GO


SELECT TextData
FROM ::fn_trace_gettable('C:\TFS_ecb\WebcastsYPresentaciones\SQLCLR\Miscelaneo\traza_2015-01-22 092136_359.trc',1)
WHERE eventclass = 12
AND dbo.BuscarTexto(TextData,'W881233') = 1
go

